<?php
App::uses('AppController', 'Controller');
/**
 * Slots Controller
 *
 * @property Slot $Slot
 * @property PaginatorComponent $Paginator
 */
class SlotsController extends AppController {

/**
 * Components
 *
 * @var array
 */
	public $components = array('Paginator', 'RequestHandler');

/**
 * Obtain a list of families per teacher or teachers per family
 **/
    public function list_slots() {
        #$this->request->onlyAllow('ajax'); // No direct access via browser URL
        $date_fmt = Configure::read('ptsignup.date_fmt');
        $time_fmt = Configure::read('ptsignup.time_fmt');
        $out = array();
        $params = array(
            'fields' => array(
                'Slot.id',
                 "DATE_FORMAT(Slot.start_time, '$date_fmt') as start_date",  
                 "DATE_FORMAT(Slot.start_time, '$time_fmt') as start_time",  
                 "DATE_FORMAT(Slot.end_time, '$time_fmt') as end_time",  
                 "Slot.teacher_id", "Teacher.display_name AS teacher_name", "Family.id AS family_id", 
                 "Family.name AS family_name",
                 "Location.id as location_id",
                 "Location.name as location_name"
            ),
        );
        $family_id = @$this->request->query['family_id'];
        if ($family_id) {
            $out['title'] = 'Family';
            $out['header'] = 'Teacher';
            #$namesql = "SELECT name FROM ".dbTable('families')." families WHERE id = $family_id";
            $namerow = $this->Slot->Family->findById($family_id);
            $out['name'] = $namerow['Family']['name'];
            $repeated_name = 'family_name';
            $diff_name = 'teacher_name';
            $params['conditions'] = array("Slot.family_id" => $family_id);
        } else {
            if (!$teacher_id = @$this->request->query['teacher_id'])
                $out = array('success' => false, 'error' => 'ERROR: Either family or teacher must be specified');
            else {
                $out['title'] = 'Teacher';
                $out['header'] = 'Family';
                #$namesql = "SELECT name FROM ".dbTable('families')." families WHERE id = $family_id";
                $namerow = $this->Slot->Teacher->findById($teacher_id);
                $out['name'] = $namerow['Teacher']['display_name'];
                $repeated_name = 'teacher_name';
                $diff_name = 'family_name';
                $params['conditions'] = array("Slot.teacher_id" => $teacher_id);
            }
        }
        $params['fields'] = str_replace(array($repeated_name, $diff_name), array('title_name', 'name'), $params['fields']);
        $school_id = @$this->Auth->user('User.school_id');
        if (!$out && !$school_id)
            $out = array("success" => false, 'error' => 'ERROR: School ID not specified. Please contact the developer');
        $tmpslots = $this->Slot->find('all', $params);
        # I have to flatten this from the inconvenient model hash that comes back from find
        $slots = array();
        foreach ($tmpslots as $slot)
            $slots[] = array_merge($slot['Slot'], $slot[0], $slot['Teacher'], $slot['Family'], $slot['Location']);
        foreach ($slots as &$slot) {
            if ($slot['location_id']) continue;
            if ($t = $this->Slot->Teacher->findById($slot['teacher_id'])) {
                #echo "t=<pre>".print_r($t,true)."</pre><br />\n";
                $slot['location_id'] = $t['Teacher']['location_id'];
                $slot['location_name'] = $t['Location']['name'];
            }
        }
        $out['success'] = true;
        $out['rows'] = $slots;
        #$out = array('success' => true);
        #$out['list'] = '<h3>Hello</h3> <p>This is a test</p>';
        $this->set('out', $out); 
        $this->set('params', $params); 
        $this->set('date_fmt', $date_fmt); 
        $this->set('time_fmt', $time_fmt); 
        #return json_encode($out); // this bypasses the default render of the view so no view is necessary
        #$this->render('ajax');
    }
/**
 * Update a slot (either signing up or unsigning up) via ajax
 **/
    public function ajax_check4changes() {
        $this->autoRender = false; // We don't render a view in this example
        #$this->request->onlyAllow('ajax'); // No direct access via browser URL
        $out = array();
        $seconds = @$this->request->query['seconds']; // Number of most recent seconds to search for changes
        $dept_id = @$this->request->query['dept_id']; // Department ID to search for changes
        $school_id = @$this->Auth->user('User.school_id');
        if (!$school_id)
            $out = array("success" => false, 'error' => 'ERROR: School ID not specified. Please contact the developer');
        if (!$out && !$dept_id)
            $out = array("success" => false, 'error' => 'ERROR: Dept ID not specified. Please contact the developer');
        if (!$out && !$seconds)
            $out = array("success" => false, 'error' => 'ERROR: Number of seconds to search not specified. Please contact the developer');
        $params = array(
            'conditions' => array(
                'Slot.school_id' => $school_id,
                'Slot.dept_id' => $dept_id,
                "Slot.modified >= (UTC_TIMESTAMP() - INTERVAL $seconds SECOND)"
            )
        );
        $changed_slots = $this->Slot->find('all', $params);
        $out = array('success' => true);
        foreach ($changed_slots as $slot)
            $out['changes'][] = array(
                'id'=>$slot['Slot']['id'], 
                'family_id'=>$slot['Slot']['family_id'], 
                'family_name'=>($slot['Slot']['family_id'] == -1 ? 'Unavailable' : @$slot['Family']['name'])
            );
        $this->set('out', $out); 
        #return json_encode($out); // this bypasses the default render of the view so no view is necessary
        $this->render('ajax');
    }
/**
 * Update a slot (either signing up or unsigning up) via ajax
 **/
    public function ajax_update() {
        $out = array();
        $slot_id = @$this->request->query['slot_id'];
        $family_id = @$this->request->query['family_id']; // '!NULL!' if unsigning up
        $school_id = @$this->Auth->user('User.school_id');
        if (!$slot_id)
            $out = array("success" => false, 'error' => 'ERROR: Slot ID not specified. Please contact the developer');
        if (!$out && !$family_id)
            $out = array("success" => false, 'error' => 'ERROR: Family ID not specified. Please contact the developer');
        if (!$out) {
            $conflicts = 0;
            if ($family_id == '!NULL!') {
                $family_id = null;
                $name = 'NULL';
            } elseif ($family_id == -1) { // unavailablj
                $name = 'Unavailable';
            } else { // normal family_id
                $this->loadModel('Family');
                if ($family_row = $this->Family->findById($family_id))
                    $name = $family_row['Family']['name'];
                else
                    $out = array('success'=>false, 'error'=>"ERROR: Unknown family ID $family_id. Cannot make changes.");
                if (!$out) {
                    $params = array(
                        'conditions' => array(
                            'Slot.family_id' => $family_id,
                            'Slot.school_id' => $school_id,
                            'NOT' => array('Slot.id' => $slot_id),
                            'OR' => array(
                                        array( # slot start_time is between b.start & b.end
                                            'Slot.start_time >= slotb.start_time',
                                            'Slot.start_time < slotb.end_time',
                                        ),
                                        array( # slot.end_time is between b.start & b.end
                                            'Slot.end_time > slotb.start_time',
                                            'Slot.end_time <= slotb.end_time',
                                        ),
                                        array( # slot starts before b.start and ends after b.end
                                            'Slot.end_time > slotb.start_time',
                                            'Slot.start_time < slotb.end_time',
                                        ),
                                    )
                        ),
                        'joins' => array(
                            array(
                                'table' => 'slots',
                                'alias' => 'slotb', 
                                'type' => 'INNER',
                                'conditions' => array('slotb.id' => $slot_id),
                            ),
                        ),
                    );
                    $conflicts = $this->Slot->find('count', $params);
                }
            }
        }
        if (!$out) {
            if (!$slot = $this->Slot->findById($slot_id))
                $out = array('success'=>false, 'error'=>"ERROR: Cannot find slot ID $slot_id");;
        }
        if (!$out) {
            if ($family_id) { // trying to SET a family_id - make sure it's not already set
                if ($slot['Slot']['family_id'] && $family_id != $slot['Slot']['family_id'])
                    $out = array('success'=>false, 'error'=>"ERROR: That slot is already taken. (Your screen is udpated periodically if javascript is running, so it probably just happened.)");
            } else { // trying to UNSET a family_id - make sure it's actually set
                if (!$slot['Slot']['family_id'])
                    $out = array('success'=>false, 'error'=>"ERROR: Internal error. You aren't signed up for that slot and so cannot unsign up for it.");
            }
        }
        if (!$out) {
            $data = array(
                'Slot' => array(
                    'id' => $slot_id,
                    'family_id' => $family_id,
                    #'modified' => 'NOW()', // not sure why this is necessary, but not updating automagically
                ),
            );
            if ($this->Slot->save($data)) {
                $out = array('success'=>true, 'error'=>'', 'conflicts'=>$conflicts, 'family_id'=>$family_id, 'name'=>$name, 'id'=>$slot_id);
            } else {
                $out = array('success'=>false, 'error'=>'ERROR: Unable to save');
            }
        }
        $this->set('out', $out); 
    }

/*************************************
 ** view() - main view for non-admins
 *************************************/
    public function view() {
        $this->loadModel('Course'); // needed for a sub-query

        #die("<pre>".print_r($this->Course,true)."</pre>");
        $dept_id = @$this->request->params['named']['dept'];
        #die("dept=$dept_id");
        $curView = 'FAMILY'; // must get a real value!
        $user_id = $this->Auth->user('User.id');
        $school_id = $this->Auth->user('User.school_id');
        $family_id = $this->Auth->user('User.family_id');
        $date_fmt = Configure::read('ptsignup.date_fmt');
        $time_fmt = Configure::read('ptsignup.time_fmt');
        #die("DEBUG: date_fmt=$date_fmt, time_fmt=$time_fmt<br />\n");

        #############
        ## Calculate list of departments
        #############
        $this->loadModel('Dept');
        $params = array(
            'fields' => array('DISTINCT Dept.id', 'Dept.name'),
            'joins' => array(
                array(
                    'table' => 'courses',
                    'alias' => 'Course', 
                    'type' => 'INNER',
                    'conditions' => array('Course.dept_id = Dept.id'),
                ),
                array(
                    'table' => 'courses_students',
                    'alias' => 'CourseStudent', 
                    'type' => 'INNER',
                    'conditions' => array('CourseStudent.course_id = Course.id'),
                ),
                array(
                    'table' => 'students',
                    'alias' => 'Student', 
                    'type' => 'INNER',
                    'conditions' => array('Student.id = CourseStudent.student_id'),
                ),
                array(
                    'table' => 'families',
                    'alias' => 'Family', 
                    'type' => 'INNER',
                    'conditions' => array('Family.id = Student.family_id'),
                ),
            ),
            'conditions' => array(
                'Dept.school_id' => $school_id,
                # We will specify $dept_id after we have checked which depts can be chosen
                # Don't check Dept.opens and Dept.closes here because it interferes with ADMIN view
            ),
            'order' => array('Family.name', 'Family.id'),
        );
        $family_param = $params;
        $family_param['conditions']['Family.id'] = $family_id;
        $family_params['conditions'][] = array('OR' => array('Dept.opens' => null, 'Dept.opens <= NOW()'));
        $family_params['conditions'][] = array('OR' => array('Dept.closes' => null, 'Dept.closes >= NOW()'));
        $family_dept_list = $this->Dept->find('all', $family_param);
        $teacher_param = $params;
        $teacher_param['conditions']['Teacher.id'] = $user_id;
        $teacher_param['conditions'][] = array('OR' => array('Dept.opens' => null, 'Dept.opens <= NOW()'));
        $teacher_param['conditions'][] = array('OR' => array('Dept.closes' => null, 'Dept.closes >= NOW()'));
        $teacher_param['joins'][] = array(
                'table' => 'teachers',
                'alias' => 'Teacher', 
                'type' => 'INNER',
                'conditions' => array('Course.teacher_id = Teacher.id'),
        );
        $teacher_dept_list = $this->Dept->find('all', $teacher_param);
        if ($this->Auth->user('User.signup_admin')) {
            $admin_dept_list = $this->Dept->find('all', $params);
        } else {
            $admin_dept_list = array();
        }
        $curView = @$this->request->params['named']['view'];
        #die('<pre>'.print_r($this->request,true).'</pre>');
        $valid_views = array();
        if ($family_dept_list) {
            $valid_views[] = 'FAMILY';
            if (!$curView || $curView == 'FAMILY') {
                $curView = 'FAMILY';
                $tmpdept = $family_dept_list;
            }
        }
        if ($teacher_dept_list) {
            $valid_views[] = 'TEACHER';
            if (!$curView || $curView == 'TEACHER') {
                $curView = 'TEACHER';
                $tmpdept = $teacher_dept_list;
            }
        }
        if ($admin_dept_list) {
            $valid_views[] = 'ADMIN';
            if (!$curView || $curView == 'ADMIN') {
                $curView = 'ADMIN';
                $tmpdept = $admin_dept_list;
            }
        } 
        $dept_list = array();
        if (!$curView) {
            $curView = 'ERROR';
        } else {
            foreach ($tmpdept as $dept) {
                if (!$dept_id)
                    $dept_id = $dept['Dept']['id']; // use 1st if not specified
                $dept_list[] = array('id'=>$dept['Dept']['id'], 'name'=>$dept['Dept']['name']);
            }
        }

        #############
        ## Calculate list of families
        #############
        # NOTE that we use the $params from above and just make minor modifications for getting the family list
        $params['fields'] = array('DISTINCT Family.id', 'Family.name');
        $params['conditions']['Dept.id'] = $dept_id; // now we want to restrict to current $dept_id
        if ($curView == 'FAMILY' && $family_id) {
            $params['conditions']['Family.id'] =  $family_id;
            $params['conditions'][] = array('OR' => array('Dept.opens' => null, 'Dept.opens <= NOW()'));
            $params['conditions'][] = array('OR' => array('Dept.closes' => null, 'Dept.closes >= NOW()'));
            $need_unavailable = false;
        }
        if ($curView == 'TEACHER') {
            $params['conditions']['Teacher.id'] =  $user_id;
            $params['conditions'][] = array('OR' => array('Dept.opens' => null, 'Dept.opens <= NOW()'));
            $params['conditions'][] = array('OR' => array('Dept.closes' => null, 'Dept.closes >= NOW()'));
            $params['joins'][] = array(
                    'table' => 'teachers',
                    'alias' => 'Teacher', 
                    'type' => 'INNER',
                    'conditions' => array('Course.teacher_id = Teacher.id'),
            );
            $need_unavailable = true;
        }
        if ($this->Auth->user('User.signup_admin') && $curView == 'ADMIN')
            $need_unavailable = true;
        if ($need_unavailable)
            $families = array(array('id'=>-1, 'name'=>'Unavailable'));
        else
            $families = array();
        $familycnt = 0;
        if ($familytmp = $this->Dept->find('all', $params)) {
            foreach ($familytmp as $family) {
                $families[] = array('id'=>$family['Family']['id'], 'name'=>$family['Family']['name']);
                $familycnt++;
            }
        }
        if (!$familycnt) {
            echo "ERROR: Something is wrong in the configuration of your system. Your login has no associated families. Aborting.<br />\n";
            #exit;
        }

        ##########
        # Calculate comments for teachers
        ##########
        if ($curView == 'FAMILY') {
            $this->loadModel('Student');
            $params = array(
                'fields' => array( 
                    'Teacher.id', 'Course.name', 'Student.fname', 'Student.lname' 
                ),
                'conditions' => array(
                    "Student.family_id" => $family_id,
                    "Course.dept_id" => $dept_id,
                    "Student.school_id" => $school_id,
                ),
                'joins' => array(
                    array(
                        'table' => 'courses_students',
                        'alias' => 'CourseStudent', 
                        'type' => 'INNER',
                        'conditions' => array('CourseStudent.student_id = Student.id'),
                    ),
                    array(
                        'table' => 'courses',
                        'alias' => 'Course', 
                        'type' => 'INNER',
                        'conditions' => array('CourseStudent.course_id = Course.id'),
                    ),
                    array(
                        'table' => 'teachers',
                        'alias' => 'Teacher', 
                        'type' => 'INNER',
                        'conditions' => array('Course.teacher_id = Teacher.id'),
                    ),
                ),
                'order' => array('Course.teacher_id', 'Course.name', 'Student.fname'),
            );
            $tmplist = $this->Student->find('all', $params);
            #die( "tmplist=<pre>".print_r($tmplist,true)."</pre><br />\n");
            $teacher_comments = array();
            foreach ($tmplist as $row) {
                $line = $row['Course']['name']." (".$row['Student']['fname'].' '.$row['Student']['lname'].')';
                if (isset($teacher_comments[$row['Teacher']['id']]))
                    $teacher_comments[$row['Teacher']['id']] .= "\n".$line;
                else
                    $teacher_comments[$row['Teacher']['id']] = $line;
            }
            #die("DEBUG: <pre>".print_r($teacher_comments,true)."</pre>\n");
        } else { # ADMIN or TEACHER
            $this->loadModel('Teacher');
            $params = array(
                'fields' => array( 
                    'Teacher.id', 'Course.name'
                ),
                'conditions' => array(
                    "Course.dept_id" => $dept_id,
                    "Course.school_id" => $school_id,
                ),
                'joins' => array(
                    array(
                        'table' => 'courses',
                        'alias' => 'Course', 
                        'type' => 'INNER',
                        'conditions' => array('Teacher.id = Course.teacher_id'),
                    ),
                ),
                'order' => array('Course.teacher_id', 'Course.name'),
            );
            if ($curView == 'TEACHER')
                $params['conditions']['Teacher.id'] = $user_id;
            $tmplist = $this->Teacher->find('all', $params);
            #die( "tmplist=<pre>".print_r($tmplist,true)."</pre><br />\n");
            $teacher_comments = array();
            foreach ($tmplist as $row) {
                $line = $row['Course']['name'];
                if (isset($teacher_comments[$row['Teacher']['id']]))
                    $teacher_comments[$row['Teacher']['id']] .= "\n".$line;
                else
                    $teacher_comments[$row['Teacher']['id']] = $line;
            }
        }

        ##########
        # Calculate primary list of slots
        ##########
        $params = array(
            'fields' => array( 
                'Slot.id', 'Slot.start_time AS real_start_time', "'' AS comment", 
                "DATE_FORMAT(Slot.start_time, '$date_fmt') as start_date",  
                "DATE_FORMAT(Slot.start_time, '$time_fmt') as start_time",  
                "DATE_FORMAT(Slot.end_time, '$time_fmt') as end_time",  
                'teacher_id', 'Teacher.display_name', 
                'Slot.family_id AS family_id', 
                "IF(Slot.family_id = -1, 'Unavailable', Family.name) as name",
            ),
            'conditions' => array(
                "Slot.school_id" => $this->Auth->user('User.school_id'),
                "Slot.dept_id" => $dept_id,
            ),
            'order' => array('Slot.start_time', 'Teacher.lname', 'Teacher.fname'),
        );

        if ($curView == 'TEACHER')
            $params['conditions']['Teacher.id'] = $this->Auth->user('User.id');
        elseif ($curView == 'FAMILY') {
            $subQueryCond = array('Student.family_id' => $family_id, 'Course.dept_id' => $dept_id);
            $db = $this->Slot->getDataSource();
            #die($db->fullTableName($this->Course),
            $subQuery = $db->buildStatement(
                array(
                    'fields' => array('Course.teacher_id'),
                    'table' => $db->fullTableName($this->Course),
                    'alias' => 'Course',
                    'joins' => array(
                        array(
                            'table' => 'courses_students',
                            'alias' => 'CourseStudent', 
                            'type' => 'LEFT',
                            'conditions' => array('CourseStudent.course_id = Course.id'),
                        ),
                        array(
                            'table' => 'students',
                            'alias' => 'Student',
                            'type' => 'LEFT',
                            'conditions' => array('Student.id = CourseStudent.student_id'),
                        ),
                    ),
                    'conditions' => $subQueryCond
                ),
                $this->Course
            );
            $subQuery = " Slot.teacher_id IN ( $subQuery )";
            $subQueryExpr = $db->expression($subQuery);
            $params['conditions'][] = $subQueryExpr;
        }
        $tmpslots = $this->Slot->find('all', $params);
        # simplify the array -- too much of a pain to process models when they are irrelevant
        $slots = array();
        foreach ($tmpslots as $tmpslot) {
            $slots[] = array_merge($tmpslot['Slot'], $tmpslot[0], $tmpslot['Teacher']);
        }

        ###########
        # Now send values off to the view
        ###########
        $this->set(compact('dept_id', 'curView', 'slots', 'user_id', 'school_id', 'family_id', 'teacher_comments', 'families', 'dept_list', 'valid_views'));
    }
/**
 * index method
 *
 * @return void
 */
	public function index() {
		$this->Slot->recursive = 0;
		$this->set('slots', $this->Paginator->paginate());
	}

/**
 * view method
 *
 * @throws NotFoundException
 * @param string $id
 * @return void
 */
	public function old_baked_view($id = null) {
		if (!$this->Slot->exists($id)) {
			throw new NotFoundException(__('Invalid slot'));
		}
		$options = array('conditions' => array('Slot.' . $this->Slot->primaryKey => $id));
		$this->set('slot', $this->Slot->find('first', $options));
	}

/**
 * add method
 *
 * @return void
 */
	public function add() {
		if ($this->request->is('post')) {
			$this->Slot->create();
			if ($this->Slot->save($this->request->data)) {
				$this->Session->setFlash(__('The slot has been saved.'));
				return $this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('The slot could not be saved. Please, try again.'));
			}
		}
		$schools = $this->Slot->School->find('list');
		$depts = $this->Slot->Dept->find('list');
		$teachers = $this->Slot->Teacher->find('list');
		$locations = $this->Slot->Location->find('list');
		$families = $this->Slot->Family->find('list');
		$this->set(compact('schools', 'depts', 'teachers', 'locations', 'families'));
	}

/**
 * edit method
 *
 * @throws NotFoundException
 * @param string $id
 * @return void
 */
	public function edit($id = null) {
		if (!$this->Slot->exists($id)) {
			throw new NotFoundException(__('Invalid slot'));
		}
		if ($this->request->is(array('post', 'put'))) {
			if ($this->Slot->save($this->request->data)) {
				$this->Session->setFlash(__('The slot has been saved.'));
				return $this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('The slot could not be saved. Please, try again.'));
			}
		} else {
			$options = array('conditions' => array('Slot.' . $this->Slot->primaryKey => $id));
			$this->request->data = $this->Slot->find('first', $options);
		}
		$schools = $this->Slot->School->find('list');
		$depts = $this->Slot->Dept->find('list');
		$teachers = $this->Slot->Teacher->find('list');
		$locations = $this->Slot->Location->find('list');
		$families = $this->Slot->Family->find('list');
		$this->set(compact('schools', 'depts', 'teachers', 'locations', 'families'));
	}

/**
 * delete method
 *
 * @throws NotFoundException
 * @param string $id
 * @return void
 */
	public function delete($id = null) {
		$this->Slot->id = $id;
		if (!$this->Slot->exists()) {
			throw new NotFoundException(__('Invalid slot'));
		}
		$this->request->allowMethod('post', 'delete');
		if ($this->Slot->delete()) {
			$this->Session->setFlash(__('The slot has been deleted.'));
		} else {
			$this->Session->setFlash(__('The slot could not be deleted. Please, try again.'));
		}
		return $this->redirect(array('action' => 'index'));
	}
}
